package IT.DataAccess;

import IT.Entities.Empleado;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import java.util.logging.Level;
import java.util.logging.Logger;


public class DatEmpleado implements IDatEmpleado{

    private Empleado oEmpleado;
    private List<Empleado> oLista;
    private BDConexion oBase;
    
    private Connection oConexion;
    private CallableStatement oCst;    
    private ResultSet oRst;    
    
    public List<Empleado> Listar() {
        oLista=new ArrayList<Empleado>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoListar()}");
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Empleado(oRst.getInt("cod_emp_in"), oRst.getString("nom_emp_vc"), 
                        oRst.getString("ape_pat_emp_vc"), oRst.getString("ape_mat_emp_vc"), 
                        oRst.getString("Empleado"),
                        oRst.getString("cor_emp_vc"), oRst.getString("tel_emp_vc"), oRst.getString("cel_emp_vc"), 
                        oRst.getInt("est_emp_in"), oRst.getString("Estado"), 
                        oRst.getInt("cod_ar_in"), oRst.getString("nom_ar_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }

    public Empleado Ind(int nCodigo){
        oEmpleado=new Empleado();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoInd(?)}");
            oCst.setInt(1, nCodigo);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oEmpleado.setCod_emp_in(oRst.getInt("cod_emp_in"));
                oEmpleado.setNom_emp_vc(oRst.getString("nom_emp_vc"));
                oEmpleado.setApe_pat_emp_vc(oRst.getString("ape_pat_emp_vc"));
                oEmpleado.setApe_mat_emp_vc(oRst.getString("ape_mat_emp_vc"));
                oEmpleado.setEmpleado(oRst.getString("Empleado"));
                oEmpleado.setCor_emp_vc(oRst.getString("cor_emp_vc"));
                oEmpleado.setTel_emp_vc(oRst.getString("tel_emp_vc"));
                oEmpleado.setCel_emp_vc(oRst.getString("cel_emp_vc"));
                oEmpleado.setEst_emp_in(oRst.getInt("est_emp_in"));
                oEmpleado.setEstado(oRst.getString("Estado"));
                oEmpleado.setCod_ar_in(oRst.getInt("cod_ar_in"));
                oEmpleado.setNom_ar_vc(oRst.getString("nom_ar_vc"));
                
                /*oLista.add(new Empleado(oRst.getInt("cod_emp_in"), 
                        oRst.getString("nom_emp_vc"), 
                        oRst.getString("ape_pat_emp_vc"), 
                        oRst.getString("ape_mat_emp_vc"), 
                        oRst.getString("Empleado"),
                        oRst.getString("cor_emp_vc"), 
                        oRst.getString("tel_emp_vc"), 
                        oRst.getString("cel_emp_vc"), 
                        oRst.getInt("est_emp_in"), 
                        oRst.getString("Estado"), 
                        oRst.getInt("cod_ar_in"), 
                        oRst.getString("nom_ar_vc")));*/
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oEmpleado;
    }

    public List<Empleado> Ubi(int nCodArea) {
        oLista=new ArrayList<Empleado>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoUbi(?)}");
            oCst.setInt(1, nCodArea);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Empleado(oRst.getInt("cod_emp_in"), oRst.getString("nom_emp_vc"), 
                        oRst.getString("ape_pat_emp_vc"), oRst.getString("ape_mat_emp_vc"), 
                        oRst.getString("Empleado"),
                        oRst.getString("cor_emp_vc"), oRst.getString("tel_emp_vc"), oRst.getString("cel_emp_vc"), 
                        oRst.getInt("est_emp_in"), oRst.getString("Estado"), 
                        oRst.getInt("cod_ar_in"), oRst.getString("nom_ar_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }
    
    public List<Empleado> SinUsuario() {
        oLista=new ArrayList<Empleado>();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoSinUsuarioListar()}");          
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oLista.add(new Empleado(oRst.getInt("cod_emp_in"), oRst.getString("nom_emp_vc"), 
                        oRst.getString("ape_pat_emp_vc"), oRst.getString("ape_mat_emp_vc"), 
                        oRst.getString("Empleado"),
                        oRst.getString("cor_emp_vc"), oRst.getString("tel_emp_vc"), oRst.getString("cel_emp_vc"), 
                        oRst.getInt("est_emp_in"), oRst.getString("Estado"), 
                        oRst.getInt("cod_ar_in"), oRst.getString("nom_ar_vc")));
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }
    
    public List<Empleado> PermisoListar(String cPermiso) {
     oLista=new ArrayList<Empleado>();
         
        try {
            oBase = new BDConexion();            
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoPermisoListar(?)}");   
            oCst.setString(1, cPermiso);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oEmpleado=new Empleado();
                oEmpleado.setCod_emp_in(oRst.getInt("cod_emp_in"));
                oEmpleado.setEmpleado(oRst.getString("Empleado"));
                oEmpleado.setNom_ar_vc(oRst.getString("nom_ar_vc"));
                oLista.add(oEmpleado);
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oLista;
    }
    
    public int Insertar(Empleado ent) {
       int nCodigo=0;
       try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoInsertar(?,?,?,?,?,?,?,?,?)}");                 
            oCst.registerOutParameter(1, java.sql.Types.INTEGER);  
            oCst.setString(2, ent.getNom_emp_vc());
            oCst.setString(3, ent.getApe_pat_emp_vc());
            oCst.setString(4, ent.getApe_mat_emp_vc());
            oCst.setString(5, ent.getCor_emp_vc());
            oCst.setString(6, ent.getTel_emp_vc());
            oCst.setString(7, ent.getCel_emp_vc());
            oCst.setInt(8, ent.getEst_emp_in());
            oCst.setInt(9, ent.getCod_ar_in());
            oCst.execute();
            nCodigo=oCst.getInt(1);
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
       return nCodigo;
    }

    public void Editar(Empleado ent) {
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoEditar(?,?,?,?,?,?,?,?,?)}");              
            oCst.setInt(1, ent.getCod_emp_in());
            oCst.setString(2, ent.getNom_emp_vc());
            oCst.setString(3, ent.getApe_pat_emp_vc());
            oCst.setString(4, ent.getApe_mat_emp_vc());
            oCst.setString(5, ent.getCor_emp_vc());
            oCst.setString(6, ent.getTel_emp_vc());
            oCst.setString(7, ent.getCel_emp_vc());
            oCst.setInt(8, ent.getEst_emp_in());
            oCst.setInt(9, ent.getCod_ar_in());
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int Eliminar(int nCodigo) {
        int nResultado=0;
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoEliminar(?,?)}");           
            oCst.setInt(1, nCodigo);
            oCst.registerOutParameter(2, java.sql.Types.INTEGER);
            oCst.execute();
            nResultado=oCst.getInt(2);
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
        return nResultado;                
    }

    public Empleado AppInd(int nCodigo) {
        oEmpleado=new Empleado();
         
        try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoAppInd(?)}");
            oCst.setInt(1, nCodigo);
            oRst = oCst.executeQuery();

            while (oRst.next()) {
                oEmpleado.setCod_emp_in(oRst.getInt("cod_emp_in"));
                oEmpleado.setEmpleado(oRst.getString("Empleado"));
                
                oEmpleado.setApp_emp_bi(oRst.getBoolean("app_emp_bi"));
                oEmpleado.setUsu_app_emp_vc(oRst.getString("usu_app_emp_vc"));
                oEmpleado.setCon_app_emp_vc(oRst.getString("con_app_emp_vc"));
                
            }
            oRst.close();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
         return oEmpleado;
    }

    public void AppEditar(Empleado ent) {
         try {
            oBase = new BDConexion();
            oConexion = oBase.getDataSource().getConnection();
            oBase = null;
            oCst = oConexion.prepareCall("{call usp_EmpleadoAppEditar(?,?,?, ?)}");              
            oCst.setInt(1, ent.getCod_emp_in());
            oCst.setBoolean(2, ent.isApp_emp_bi());            
            oCst.setString(3, ent.getUsu_app_emp_vc());
            oCst.setString(4, ent.getCon_app_emp_vc());            
       
            oCst.execute();
            oCst.close();
            oConexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatArea.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
